# 网元PON口筛选
# 注意列   以及  赋值的列！！！！！
import openpyxl
import re


def getData(filename):
    wb = openpyxl.load_workbook(filename)
    ws = wb.active

    columnList = ['L:O'] 
    result = []
    for x in columnList:
        column1 = ws[x]
        for i in range(len(column1)):
            d = []
            for j in column1[i]:
                d.append(j.value)
            result.append(d)
    return result

def modify(filename, result):
    wb = openpyxl.load_workbook(filename)
    ws = wb.active
    equipment = result[0]
    port = result[2]
    alarmName = result[3]
    for i in range(1,len(port)):
        if alarmName[i] in ['主干光纤断或OLT检测不到预期的光信号（LOS）','GPON端口光模块硬件故障','PON口光模块不在位','光模块不在位','TXFAIL','LINKDOWN','上行以太端口连线故障或端口状态异常','上联口链路中断','以太光口接收信号丢失']:
            print('是主干类')
            # （1）上报*
            regular1 = re.compile(r'上报.*\n.*')
            # （2）*;
            regular2 = re.compile(r'.*;')
            # （3） .*\n
            regular3 = re.compile(r'.*\n')
            str1 = port[i]
            str2 = regular1.sub('',str1)
            str3 = regular2.sub('',str2)
            str4 = regular3.sub('',str3)
            result[2][i] = str4
            ws.cell(row=i+1,column=14,value=result[2][i])
        elif alarmName[i] in ['OLT上联口部分受阻','OLT上联口全阻']:
            # （1）*：
            regular1 = re.compile(r'.*\n.*:')
            # （2） ,*
            regular2 = re.compile(r',.*\n.*')
            # （3）.*\n
            regular3 = re.compile(r'.*\n')
            str1 = port[i]
            str2 = regular1.sub('',str1)
            str3 = regular2.sub('',str2)
            str4 = regular3.sub('',str3)
            result[2][i] = str4
            ws.cell(row=i+1,column=14,value=result[2][i])
        elif alarmName[i] in ['PONLOS']:
            print('PONLOS')
            # （1）:PON信号丢失.*
            regular1 = re.compile(r':PON信号丢失.*\n.*')
            # （2）*:
            regular2 = re.compile(r'.*\n.*:')
            # （3）.*\n
            regular3 = re.compile(r'.*\n')
            # （4） R
            regular4 = re.compile(r'R')
            # （5） .S
            regular5 = re.compile(r'\.S')
            # （6） .LT 
            regular6 = re.compile(r'\.LT')
            # （7） .PON
            regular7 = re.compile(r'\.PON')
            str1 = port[i]
            str2 = regular1.sub('',str1)
            str3 = regular2.sub('',str2)
            str4 = regular3.sub('',str3)
            str5 = regular4.sub('',str4)
            str6 = regular5.sub('-',str5)
            str7 = regular6.sub('-',str6)
            str8 = regular7.sub('-',str7)
            result[2][i] = str8
            ws.cell(row=i+1,column=14,value=result[2][i])
        elif alarmName[i] in ['[GPON告警]PON信号丢失']:
            print('信号丢失')
            regular = re.compile(r'.*,an_rack=')
            regular1 = re.compile(r':.*\n.*')
            regular2 = re.compile(r',an_shelf=')
            regular3 = re.compile(r',an_slot=')
            regular4 = re.compile(r',an_port=')
            regular5 = re.compile(r'.*\n')
            str1 = port[i]
            str2 = regular.sub('',str1)
            str3 = regular1.sub('',str2)
            str4 = regular2.sub('-',str3)
            str5 = regular3.sub('-',str4)
            str6 = regular4.sub('-',str5)
            str7 = regular5.sub('',str6)
            result[2][i] = str7
            ws.cell(row=i+1,column=14,value=result[2][i])
        else:
            continue
        
    wb.save(filename)
    #return result

if __name__ == "__main__":
    filename = r'C:\Users\tujuan\Desktop\故障周报\8月故障周报\8.5-8.11\8.5-8.11(整理).xlsx'
    data = getData(filename)
    modify(filename,data)
    print('数据已修改')